/*DONE* Table [dbo].[<CompanyName>$LSC Store$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 30.7.2020 - JI ******/
/*Add prestaging table for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT MAX([Company]) FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$LSC Store$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$LSC Store$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](10) NOT NULL,
	[Responsibility Center] [nvarchar](10) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Address] [nvarchar](100) NOT NULL,
	[Address 2] [nvarchar](50) NOT NULL,
	[City] [nvarchar](30) NOT NULL,
	[Post Code] [nvarchar](20) NOT NULL,
	[Store Manager ID] [nvarchar](20) NOT NULL,
	[Phone No_] [nvarchar](30) NOT NULL,
	[Country Code] [nvarchar](10) NOT NULL,
	[Global Dimension 1 Code] [nvarchar](20) NOT NULL,
	[Location Code] [nvarchar](10) NOT NULL,
	[Currency Code] [nvarchar](10) NOT NULL,
	[Global Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Store Type] [int] NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Functionality Profile] [nvarchar](10) NOT NULL,
	[Menu Profile] [nvarchar](10) NOT NULL,
	[Interface Profile] [nvarchar](10) NOT NULL,
	[Fraud Sort Field] [decimal](38, 20) NOT NULL,
	[Style Profile] [nvarchar](20) NOT NULL,
	[Hardware Profile] [nvarchar](10) NOT NULL,
	[No_ Series] [nvarchar](10) NOT NULL,
	[Statement Nos_] [nvarchar](10) NOT NULL,
	[Posted Statem_ Nos_] [nvarchar](10) NOT NULL,
	[One Statement per Day] [tinyint] NOT NULL,
	[Statement Method] [int] NOT NULL,
	[Closing Method] [int] NOT NULL,
	[Rounding Account] [nvarchar](20) NOT NULL,
	[Max_ Diff_ to Allow Post_] [decimal](38, 20) NOT NULL,
	[Max_ Round_ in Stmt_] [decimal](38, 20) NOT NULL,
	[Max_ Diff_ from Shift Date] [decimal](38, 20) NOT NULL,
	[Allowed Diff_ in Trans_] [decimal](38, 20) NOT NULL,
	[Paym_ Compr_ in Statem_ Post_] [tinyint] NOT NULL,
	[Tend_ Decl_ Calculation] [int] NOT NULL,
	[Advanced Shift Method] [tinyint] NOT NULL,
	[Advanced Shift Nos_] [nvarchar](20) NOT NULL,
	[Default Commission Group] [nvarchar](10) NOT NULL,
	[Campaign Nos_] [nvarchar](20) NOT NULL,
	[Promotion Nos_] [nvarchar](20) NOT NULL,
	[Periodic Discount Nos_] [nvarchar](20) NOT NULL,
	[Published Offer Nos_] [nvarchar](20) NOT NULL,
	[Member Notification Nos_] [nvarchar](20) NOT NULL,
	[FBP Nos_] [nvarchar](20) NOT NULL,
	[POS Terminal Nos_] [nvarchar](20) NOT NULL,
	[Staff ID Nos_] [nvarchar](20) NOT NULL,
	[Coupon Nos_] [nvarchar](20) NOT NULL,
	[Item Nos_] [nvarchar](20) NOT NULL,
	[Data Access Control] [tinyint] NOT NULL,
	[Store VAT Bus_ Post_ Gr_] [nvarchar](20) NOT NULL,
	[Store Gen_ Bus_ Post_ Gr_] [nvarchar](10) NOT NULL,
	[Language Code] [nvarchar](10) NOT NULL,
	[POS Inventory Lookup] [tinyint] NOT NULL,
	[POS Warmup Active] [tinyint] NOT NULL,
	[Click and Collect] [tinyint] NOT NULL,
	[Print Receipt Logo] [nvarchar](80) NOT NULL,
	[Print Receipt Bitmap No_] [int] NOT NULL,
	[Rcpt_ Text Max_ Length] [int] NOT NULL,
	[No_ of Top_Bottom Lines] [int] NOT NULL,
	[Item No_ on Receipt] [int] NOT NULL,
	[Last Action for Label Upd] [int] NOT NULL,
	[Last Action for Item Label Upd] [int] NOT NULL,
	[No Shelf Label Printing] [tinyint] NOT NULL,
	[No Item Label Printing] [tinyint] NOT NULL,
	[Service Charge _] [decimal](38, 20) NOT NULL,
	[Serv_ Charge_ Inc_Exp Acc] [nvarchar](10) NOT NULL,
	[Safe Mgnt_ in Use] [tinyint] NOT NULL,
	[Latitude] [decimal](38, 20) NOT NULL,
	[Longitude] [decimal](38, 20) NOT NULL,
	[County] [nvarchar](30) NOT NULL,
	[Picture] [image] NULL,
	[Picture Media] [uniqueidentifier] NOT NULL,
	[POS Check Z-Report] [tinyint] NOT NULL,
	[Store size (square meters)] [int] NOT NULL,
	[Loading Card Nos_] [nvarchar](10) NOT NULL,
	[Receiving Nos_] [nvarchar](10) NOT NULL,
	[Posted Receiving Nos_] [nvarchar](10) NOT NULL,
	[Gift Registration Nos_] [nvarchar](10) NOT NULL,
	[Use Batch Posting for Statem_] [tinyint] NOT NULL,
	[Only Accept Statement] [tinyint] NOT NULL,
	[Max_ Diff_ to Allow Accept] [decimal](38, 20) NOT NULL,
	[Current Hosp_ Printing Route] [nvarchar](10) NOT NULL,
	[POS Start Amount Method] [int] NOT NULL,
	[POS Post Safe Ledger] [tinyint] NOT NULL,
	[Dining Reservation Nos_] [nvarchar](10) NOT NULL,
	[Dining Waiting List Nos_] [nvarchar](10) NOT NULL,
	[Dining Walk-in Nos_] [nvarchar](10) NOT NULL,
	[KDS Backup Printing] [int] NOT NULL,
	[Active KDS Load Config_] [nvarchar](20) NOT NULL,
	[Default Delivery Type] [int] NOT NULL,
	[Display Del_ Type Pop-up] [tinyint] NOT NULL,
	[Ordering Status] [int] NOT NULL,
	[Delivery_Takeout Change] [int] NOT NULL,
	[Rest_ Orders in Progress] [int] NOT NULL,
	[Rest_ Estim_ Prod_ Time (Min_)] [decimal](38, 20) NOT NULL,
	[Kitchen Prod_ System in Use] [int] NOT NULL,
	[Normal Order Timing] [int] NOT NULL,
	[Max_ Order Timing (Min_)] [int] NOT NULL,
	[Time Rounding Precision] [int] NOT NULL,
	[Location Profile] [nvarchar](30) NOT NULL,
	[Location Url] [nvarchar](250) NOT NULL,
	[LocationUrlExists] [tinyint] NOT NULL,
	[LocationQRCode] [image] NULL,
	[Store Sales Type Filter] [nvarchar](250) NOT NULL,
	[Show Availab_ on POS Button] [int] NOT NULL,
	[Attrib 1 Code] [nvarchar](30) NOT NULL,
	[Attrib 2 Code] [nvarchar](30) NOT NULL,
	[Attrib 3 Code] [nvarchar](30) NOT NULL,
	[Attrib 4 Code] [nvarchar](30) NOT NULL,
	[Attrib 5 Code] [nvarchar](30) NOT NULL,
	[In-Transit Code] [nvarchar](10) NOT NULL,
	[Web Store] [tinyint] NOT NULL,
	[Web Store POS Terminal] [nvarchar](10) NOT NULL,
	[Web Store Staff ID] [nvarchar](20) NOT NULL,
	[Web Store Customer No_] [nvarchar](20) NOT NULL,
	[Web Store Shipping Cost Item] [nvarchar](20) NOT NULL,
	[Loyalty] [tinyint] NOT NULL,
	[Mobile] [tinyint] NOT NULL,
	[PLU Menu Profile] [nvarchar](10) NOT NULL,
	[PLU Menu ID] [nvarchar](10) NOT NULL,
	[Last Action Entry No_] [int] NOT NULL,
	[Prepay Customer Order] [int] NOT NULL,
	[Customer Order Inc_Expense Acc] [nvarchar](10) NOT NULL,
	[Customer Order Refund Acc] [nvarchar](20) NOT NULL,
	[Cust_ Ord_ Refund Tender Type] [nvarchar](10) NOT NULL,
	[Use Shelf Position] [tinyint] NOT NULL,
	[CO To Pick Indication] [int] NOT NULL,
	[CO To Pick Interval] [int] NOT NULL,
	[Tax Area Code] [nvarchar](20) NOT NULL,
	[Tax Liable] [tinyint] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
	[Auto_ Calculate Statement] [tinyint] NOT NULL,
	[Group by Staff_POS] [tinyint] NOT NULL,
	[PLB Store] [tinyint] NOT NULL,
	[Include SPG in Collect Panel] [tinyint] NOT NULL,
 CONSTRAINT ['+@Company+'$LSC Store$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$LSC Store$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i+1
END